<?php

	include_once "common.php"; //MUST ALWAYS BE THE LAST INCLUDE
//----------------------------------------------------------------------

	function lookup_create_item($obj){
		$db = new CDatabase();
		$sql_table = $obj->sql_table;
		$sql_fields = "";
		$sql_values = "";
		$sql_where = "";
		$pk_field = "";
		
		foreach($obj->fields as $field){
			if (!property_exists($field, "primary_key") || (property_exists($field, "primary_key") && $field->primary_key == false)){
				if ($sql_fields != "") $sql_fields .= ",";
				$sql_fields .= $field->name;
				
				if ($sql_values != "") $sql_values .= ",";
				foreach($obj->data as $key => $value){
					if ($key == $field->name){
						if ($value != null)
							$sql_values .= "".db_format($value, $field->data_type);
						else
							$sql_values .= "NULL";
					}
				}
				
				if ($sql_where != "") $sql_where .= " AND ";
				foreach($obj->data as $key => $value){
					if ($key == $field->name){
						if ($value != null)
							$sql_where .= $key. " = ".db_format($value, $field->data_type);
						else
							$sql_where .= $key. " IS NULL";
					}
				}
			}
			else if ((property_exists($field, "primary_key") && $field->primary_key == true)){
				$pk_field = $field->name;
			}
		}
		
		$db->run_statement("INSERT INTO $sql_table($sql_fields) VALUES($sql_values)");
			
		//FIRST GET THE ID AND SET IT ON THE OBJECT
		$rst = $db->run_query("SELECT $pk_field FROM $sql_table WHERE $sql_where");
		//echo "SELECT $pk_field FROM $sql_table WHERE $sql_where";
		$obj->id = -1;
		if (count($rst) > 0){
			$obj->id = $rst[0][$pk_field];
		}
		
		$sql_result = lookup_read($obj, true, false);
			
		exit_with_response(mysql_errno(), mysql_error(), $sql_result);
	}

	function lookup_update_item($obj){
		$db = new CDatabase();
		
		//can_perform_function(__FUNCTION__, $obj);

		$sql_table = $obj->sql_table;
		$sql_fields = "";
		$sql_values = "";
		$sql_where = "";
		foreach($obj->fields as $field){
			if (!property_exists($field, "primary_key") || (property_exists($field, "primary_key") && $field->primary_key == false)){
				if (!property_exists($field, "readonly") || (property_exists($field, "readonly") && $field->readonly == false)){
					if ($sql_values != "") $sql_values .= ",";
					foreach($obj->data as $key => $value){
						if ($key == $field->name){
							if ($value != null)
								$sql_values .= $field->name." = ".db_format($value, $field->data_type);
							else
								$sql_values .= $field->name." = NULL ";
						}
					}
				}
			} else if (property_exists($field, "primary_key") && $field->primary_key == true){
				if ($sql_where != "") $sql_where .= " AND ";
				foreach($obj->data as $key => $value){
					if (property_exists($field, "alias") && $key == $field->alias){
						$sql_where .= $field->name. " = ".db_format($value, $field->data_type);
					} else if ($key == $field->name){
						$sql_where .= $field->name. " = ".db_format($value, $field->data_type);
					}
				}
			}
			
		}
		$sql = "UPDATE $sql_table SET $sql_values WHERE $sql_where";
		//echo "$sql\r\n\r\n";
		$db->run_statement($sql);
		$sql_result = lookup_read($obj, true, false);
			
		exit_with_response(mysql_errno(), mysql_error(), $sql_result);
	}

	function lookup_remove_item($obj){
		$db = new CDatabase();
		//can_perform_function(__FUNCTION__, $obj);

		$sql_table = $obj->sql_table;
		$sql_where = "";
		foreach($obj->fields as $field){
			if (property_exists($field, "primary_key") && $field->primary_key == true){
				if ($sql_where != "") $sql_where .= " AND ";
				foreach($obj->data as $key => $value){
					if (property_exists($field, "alias") && $key == $field->alias){
						$sql_where .= $field->name. " = ".db_format($value, $field->data_type);
					} else if ($key == $field->name){
						$sql_where .= $field->name. " = ".db_format($value, $field->data_type);
					}
				}
			}
		}
		$sql = "DELETE FROM $sql_table WHERE $sql_where";
		//echo $sql;
		$result = $db->run_statement($sql);
		exit_with_response(mysql_errno(), mysql_error());
	}
	
	function lookup_read($obj, $single_record=false, $echo_output=true){
		$result = sql_query_to_json(get_sql_for_object($obj, $single_record));
		if (true == $echo_output)
			exit_with_response(0, "", $result);
		else
			return $result;
	}
	
	function refresh_cache($obj){
		$db = new CDatabase();
		$result = array();

		$obj->sql_table = "clientratetypes";
		$sql = "SELECT * FROM ".$obj->sql_table;
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$obj->sql_table = "suspend_types";
		$sql = "SELECT * FROM ".$obj->sql_table;
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$obj->sql_table = "titles";
		$sql = "SELECT * FROM ".$obj->sql_table;
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$obj->sql_table = "countries";
		$sql = "SELECT * FROM ".$obj->sql_table;
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$obj->sql_table = "servicetypes";
		$sql = "SELECT * FROM ".$obj->sql_table;
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$obj->sql_table = "time_display_format";
		$sql = "SELECT * FROM ".$obj->sql_table;
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$obj->sql_table = "security_types";
		$sql = "SELECT * FROM ".$obj->sql_table." ORDER BY UPPER(name) ASC";
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$obj->sql_table = "overtime_types";
		$sql = "SELECT * FROM ".$obj->sql_table;
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$obj->sql_table = "holidayruletypes";
		$sql = "SELECT * FROM ".$obj->sql_table;
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$obj->sql_table = "workgroup_overtime_rules";
		$sql = "SELECT * FROM ".$obj->sql_table;
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$obj->sql_table = "sys_attendhourtypes";
		$sql = "SELECT * FROM ".$obj->sql_table;
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$obj->sql_table = "homelanguages";
		$sql = "SELECT * FROM ".$obj->sql_table;
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$obj->sql_table = "employmenttypes";
		$sql = "SELECT * FROM ".$obj->sql_table;
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$obj->sql_table = "jobmovementtypes";
		$sql = "SELECT * FROM ".$obj->sql_table;
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$obj->sql_table = "maritalstatus";
		$sql = "SELECT * FROM ".$obj->sql_table;
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$obj->sql_table = "ethnicgroups";
		$sql = "SELECT * FROM ".$obj->sql_table;
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$obj->sql_table = "disabilitytypes";
		$sql = "SELECT * FROM ".$obj->sql_table;
		$result[$obj->sql_table] = $db->run_query($sql);

		$obj->sql_table = "employee_leave_types";
		$sql = "SELECT * FROM absenttypes WHERE id <= 12 ORDER BY sort_order ASC";
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$result["shift_allowance_types"] = array(
						  array("id"=>1,"name"=>"hours(s)")
						, array("id"=>0,"name"=>"%"));

		$result["weekdays"] = array(
			  array("id"=>0	, "name"=>"Sunday")
			, array("id"=>1	, "name"=>"Monday")
			, array("id"=>2	, "name"=>"Tuesday")
			, array("id"=>3	, "name"=>"Wednesday")
			, array("id"=>4	, "name"=>"Thursday")
			, array("id"=>5	, "name"=>"Friday")
			, array("id"=>6	, "name"=>"Saturday")
		);

		//CACHE BELOW THIS LINE CAN ONLY BE LOADED IF THERE IS A VALID CLIENT
		$sql = "SELECT * FROM clients WHERE id = ".db_format($obj->client);
		$rst = $db->run_query($sql);
		if (count($rst) == 0){
			exit_with_response(0,"", json_encode($result));
		}
		
		$obj->sql_table = "projects";
		$result[$obj->sql_table] = $db->run_query(get_sql_for_object($obj, false));
		
		$obj->sql_table = "security_roles";
		$result[$obj->sql_table] = $db->run_query(get_sql_for_object($obj, false));
		
		$obj->sql_table = "jobtitles";
		$result[$obj->sql_table] = $db->run_query(get_sql_for_object($obj, false));
		
		$obj->sql_table = "departments";
		$result[$obj->sql_table] = $db->run_query(get_sql_for_object($obj, false));
		
		$obj->sql_table = "workhours";
		$result[$obj->sql_table] = $db->run_query(get_sql_for_object($obj, false));
		
		$obj->sql_table = "workgroups";
		$result[$obj->sql_table] = $db->run_query(get_sql_for_object($obj, false));
		
		$obj->sql_table = "departments";
		$result[$obj->sql_table] = $db->run_query(get_sql_for_object($obj, false));
		
		$obj->sql_table = "buildings";
		$result[$obj->sql_table] = $db->run_query(get_sql_for_object($obj, false));
		
		$obj->sql_table = "pay_categories";
		$result[$obj->sql_table] = $db->run_query(get_sql_for_object($obj, false));
		
		$obj->sql_table = "members";
		$sql = "SELECT A.id, firstname, surname, CONCAT(firstname, ' ', surname) as fullname
				, workgroup, A.building, A.jobtitle, A.workgroup
				, A.department, A.datehired, A.expirydate, A.payrollnumber
				, A.pin
				FROM members A
				INNER JOIN jobmovementtypes B ON A.job_movement_type = B.id
				WHERE A.client = ".db_format($obj->client)."
				AND B.isactive = 1
				ORDER BY UPPER(firstname) ASC";
		$result[$obj->sql_table] = $db->run_query($sql);
		
		$obj->sql_table = "administrators";
		$sql = "SELECT A.id, A.firstname, A.surname, A.username
				FROM administrators A
				INNER JOIN administrator_clients B ON A.id = B.administrator
				WHERE B.client = ".db_format($obj->client)."
				AND B.security_role_given IS NOT NULL
				ORDER BY UPPER(username) ASC";
		$result[$obj->sql_table] = $db->run_query($sql);
		

		exit_with_response(0,"", json_encode($result));
	}
?>
